/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package ve.usb.cep.db.models;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import ve.usb.cep.db.SelectObject;
import ve.usb.cep.db.SelectException;
import ve.usb.cep.db.Session;
import ve.usb.cep.db.domainObjects.Curso;
import ve.usb.cep.db.modelExceptions.CursoException;
import ve.usb.cep.util.sqlWhere;

/**
 *
 * @author Andras
 */
public class CursoModel{
    Connection conn;
    PreparedStatement rowCountStatement;

    public Curso set(Curso curso)
        throws CursoException
    {
        if (0 == curso.getCodigo()){
            add(curso);
        }else{
            update(curso);
        }

        return curso;
    }

    public Curso add(Curso curso)
        throws CursoException
    {

        conn = Session.connection();

        try{
            PreparedStatement pstmt = conn.prepareStatement("INSERT INTO curso " +
                "(nombre, costo, categoria, descripcion, horas_dur)" +
                " VALUES (?, ?, CAST(? as categoria), ?, ?)");


            pstmt.setString(1, curso.getNombre());
            pstmt.setInt(2, curso.getCosto());
            pstmt.setString(3, curso.getCategoria());
            pstmt.setString(4, curso.getDescripcion());
            pstmt.setShort(5, curso.getHorasDur());
            pstmt.executeUpdate();
        }catch(java.sql.SQLException e){
            e.printStackTrace();
            throw new CursoException(e.getMessage());
        }
        return curso;
    }

    public Curso update(Curso curso)
        throws CursoException
    {

        conn = Session.connection();

        try{
            PreparedStatement pstmt = conn.prepareStatement("UPDATE curso " +
              "SET nombre=?, costo=?, categoria=CAST(? as categoria), descripcion=?, horas_dur=? " +
              " WHERE codigo=?");

            pstmt.setString(1, curso.getNombre());
            pstmt.setInt(2, curso.getCosto());
            pstmt.setString(3, curso.getCategoria());
            pstmt.setString(4, curso.getDescripcion());
            pstmt.setShort(5, curso.getHorasDur());
            pstmt.setInt(6, curso.getCodigo());
            pstmt.executeUpdate();
        }catch(java.sql.SQLException e){
            e.printStackTrace();
            throw new CursoException(e.getMessage());
        }
        return curso;
    }

    public void delete(int codigo)
        throws CursoException
    {
        conn = Session.connection();
        try{
            PreparedStatement p = conn.prepareStatement("DELETE FROM curso WHERE codigo=?");
            p.setInt(1, codigo);
            p.executeUpdate();
        }catch(SQLException e){
            e.printStackTrace();
            throw new CursoException(e.getMessage());
        }
    }

    public Curso getUniqueNombre(String nombre, int codigo)
        throws CursoException
    {
        Curso u = getByNombre(nombre);
        if (null == u){
            return null;
        }
        if (u.getCodigo() == codigo){
            return null;
        }
        return u;
    }
    public Curso getByNombre(String nombre)
       throws CursoException
    {
        return getByField("nombre", nombre);
    }
    protected Curso getByField(String field, String value)
       throws CursoException
    {
        sqlWhere w = new sqlWhere();
        sqlWhere.rule[] c = new sqlWhere.rule[1];
        c[0] = w.new rule();
        c[0].setField(field);
        c[0].setOp("eq");
        c[0].setData(value);
        w.setGroupOp(" AND ");
        w.setRules(c);
        
        ArrayList<Curso> u = getCustom(new SelectObject((short) 1, (short) 1, "", "", w));
        if (0 == u.size()){
            return null;
        }else{
            return u.get(0);
        }
    }
    public ArrayList<Curso> getCustom(SelectObject s) 
            throws CursoException {

        conn = Session.connection();
        s.setConnection(conn);
        s.setFields(Curso.getFields());
        s.setTypes(Curso.getTypes());
        s.setTable("curso");

        PreparedStatement p;
        try{
            p = s.getSearchQuery();
            rowCountStatement = s.getCountQuery();
        }catch(SelectException e){
            e.printStackTrace();
            throw new CursoException(e.getMessage());
        }

        ArrayList<Curso> arr = new ArrayList<Curso>();
        try{
            ResultSet rs = p.executeQuery();
            while (rs.next()){
                arr.add(new Curso(
                    rs.getInt("codigo"),
                    rs.getString("nombre"),
                    rs.getInt("costo"),
                    rs.getString("categoria"),
                    rs.getString("descripcion"),
                    rs.getShort("horas_dur")
                ));
            }
        }catch(SQLException e){
            e.printStackTrace();
            throw new CursoException(e.getMessage());
        }

        return arr;
    }

    public int getRowCount()
        throws CursoException
    {
        try{
            ResultSet rs = rowCountStatement.executeQuery();
            rs.next();
            return rs.getInt(1);
        }catch(SQLException e){
            e.printStackTrace();
            throw new CursoException(e.getMessage());
        }
    }
}